/*============================================================================
  File:     CreateFileGroups.sql

  Summary:  Creates 2 new file groups for the AdventureWorks sample database.
            Use "EXEC sp_helpdb AdventureWorks" to verify the results.

  Date:     July 13, 2005
------------------------------------------------------------------------------
  This file is part of the Microsoft SQL Server Code Samples.

  Copyright (C) Microsoft Corporation.  All rights reserved.

  This source code is intended only as a supplement to Microsoft
  Development Tools and/or on-line documentation.  See these other
  materials for detailed information regarding Microsoft code samples.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- Add new WorkOrder file group
ALTER DATABASE [AdventureWorks] 
ADD FILEGROUP [WorkOrderGroup];
GO

DECLARE @sql_path NVARCHAR(256);

-- Get the file path for the master database files
SELECT @sql_path = SUBSTRING([physical_name], 1, CHARINDEX(N'master.mdf', LOWER([physical_name])) - 1)
FROM [master].[sys].[master_files] 
WHERE [database_id] = 1 
    AND [file_id] = 1;

-- Create WorkOrderGroup file group
EXECUTE (N'ALTER DATABASE [AdventureWorks] ADD FILE(NAME = ''WorkOrders'', 
    FILENAME = ''' + @sql_path + 'AWWorkOrders_Data.NDF'', SIZE = 1, FILEGROWTH = 8) 
    TO FILEGROUP [WorkOrderGroup]');
GO

USE AdventureWorks;
GO

-- Move WorkOrder tables to new file group
ALTER TABLE [Production].[WorkOrderRouting] 
DROP CONSTRAINT [FK_WorkOrderRouting_WorkOrder_WorkOrderID], 
    CONSTRAINT [FK_WorkOrderRouting_Location_LocationID];
GO

ALTER TABLE [Production].[WorkOrderRouting] 
DROP CONSTRAINT [PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence] 
WITH (ONLINE = OFF, MOVE TO [WorkOrderGroup]);
GO

ALTER TABLE [Production].[WorkOrder] 
DROP CONSTRAINT [PK_WorkOrder_WorkOrderID];
GO

ALTER TABLE [Production].[WorkOrder] WITH CHECK ADD 
	CONSTRAINT [PK_WorkOrder_WorkOrderID] PRIMARY KEY CLUSTERED 
	(
		[WorkOrderID]
	) ON [WorkOrderGroup];
GO

ALTER TABLE [Production].[WorkOrderRouting] WITH CHECK ADD 
	CONSTRAINT [PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence] PRIMARY KEY CLUSTERED 
	(
		[WorkOrderID],
		[ProductID],
		[OperationSequence]
	) ON [WorkOrderGroup];
GO

ALTER TABLE [Production].[WorkOrderRouting] WITH CHECK ADD 
    CONSTRAINT [FK_WorkOrderRouting_Location_LocationID] FOREIGN KEY 
    (
        [LocationID]
    ) REFERENCES [Production].[Location](
        [LocationID]
    ),
    CONSTRAINT [FK_WorkOrderRouting_WorkOrder_WorkOrderID] FOREIGN KEY 
    (
        [WorkOrderID]
    ) REFERENCES [Production].[WorkOrder](
        [WorkOrderID]
	);
GO

-- Add new TransactionHistory file group
ALTER DATABASE [AdventureWorks] 
ADD FILEGROUP [TransactionHistoryGroup];
GO

DECLARE @sql_path NVARCHAR(256);

-- Get the file path for the master database files
SELECT @sql_path = SUBSTRING([physical_name], 1, CHARINDEX(N'master.mdf', LOWER([physical_name])) - 1)
FROM [master].[sys].[master_files] 
WHERE [database_id] = 1 
    AND [file_id] = 1;

-- Create TransactionHistoryGroup file group
EXECUTE (N'ALTER DATABASE [AdventureWorks] ADD FILE(NAME = ''TransactionHistory'', 
    FILENAME = ''' + @sql_path + 'AWTransactionHistory_Data.NDF'', SIZE = 1, FILEGROWTH = 8) 
    TO FILEGROUP [TransactionHistoryGroup]');
GO

-- Move [AdventureWorks].Production.TransactionHistory tables to new file group
ALTER TABLE [Production].[TransactionHistory] 
DROP CONSTRAINT [PK_TransactionHistory_TransactionID];
GO

ALTER TABLE [Production].[TransactionHistoryArchive] 
DROP CONSTRAINT [PK_TransactionHistoryArchive_TransactionID];
GO

ALTER TABLE [Production].[TransactionHistory] WITH CHECK ADD 
	CONSTRAINT [PK_TransactionHistory_TransactionID] PRIMARY KEY  CLUSTERED 
	(
		[TransactionID]
	)  ON [TransactionHistoryGroup];
GO

ALTER TABLE [Production].[TransactionHistoryArchive] WITH CHECK ADD 
	CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY  CLUSTERED 
	(
		[TransactionID]
	)  ON [TransactionHistoryGroup];
GO

CREATE INDEX [IX_TransactionHistory_ProductID] ON [Production].[TransactionHistory]([ProductID]) 
WITH (DROP_EXISTING = ON) ON [TransactionHistoryGroup];

CREATE INDEX [IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID] ON [Production].[TransactionHistory]([ReferenceOrderID], [ReferenceOrderLineID]) 
WITH (DROP_EXISTING = ON)  ON [TransactionHistoryGroup];
GO

CREATE INDEX [IX_TransactionHistoryArchive_ProductID] ON [Production].[TransactionHistoryArchive]([ProductID]) 
WITH (DROP_EXISTING = ON) ON [TransactionHistoryGroup];

CREATE INDEX [IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID] ON [Production].[TransactionHistoryArchive]([ReferenceOrderID], [ReferenceOrderLineID]) 
WITH (DROP_EXISTING = ON) ON [TransactionHistoryGroup];
GO

USE master
GO
